﻿CREATE PROCEDURE s_ShowWFKids_Old
	@GroupName sysname = 'Admin',
	@GrandParentID int = -1,
	@MenuID int = 0,
	@Tabs int = 0
AS
SET NOCOUNT ON
DECLARE @CR varchar(2) SET @CR = '
'
DECLARE @MenuName sysname,@KidID int,@Kids nvarchar(max),@WF int,@Color varchar(50)

IF @GrandParentID = -1 BEGIN
IF OBJECT_ID('tempdb..#ShowWFKids') IS NULL	CREATE TABLE #ShowWFKids(GrandParentID int,MenuID int,KidID int,Tabs int)
	CREATE TABLE #Nodes(Node nvarchar(1024))
	DECLARE cur CURSOR LOCAL READ_ONLY FOR
	SELECT DISTINCT wf.MenuID, wf.MenuPath, wf.WFIndex,
				 CASE WHEN wfg.GroupName IS NULL THEN 'COLOR:GRAY' ELSE '' END Color
	FROM v_WorkFlow AS wf	LEFT OUTER JOIN t_Workflow_Groups AS wfg
	ON wf.MenuID = wfg.MenuID AND wf.NextMenuID = wfg.NextMenuID AND GroupName = @GroupName 
	WHERE wfg.MenuID > 0 AND wfg.NextMenuID > 0 AND wf.MenuID <> 25 ORDER BY wf.WFIndex
	OPEN cur
	FETCH NEXT FROM cur INTO @MenuID,@MenuName,@WF,@Color
	WHILE @@fetch_status = 0 BEGIN
		--PRINT '<MYTREE:TREENODE text="'+@MenuName+'">'+@CR
		INSERT INTO #Nodes VALUES('<MYTREE:TREENODE DEFAULTSTYLE="'+@Color+'" tabID="'+CONVERT(varchar,@MenuID)+'" id="'+CONVERT(varchar,@MenuID)+'" text="'+@MenuName+'">'+@CR)
		EXEC s_ShowWFKids @GroupName,@MenuID,@MenuID,1
		--PRINT '</MYTREE:TREENODE>'+@CR
		INSERT INTO #Nodes VALUES('</MYTREE:TREENODE>'+@CR)
		FETCH NEXT FROM cur INTO @MenuID,@MenuName,@WF,@Color
	END
	CLOSE cur
	DEALLOCATE cur
	SELECT * FROM #Nodes
	RETURN
END


DECLARE @Pos int,@Tabs_ int,@Tab char SELECT @Pos=0,@Tabs_=@Tabs+1,@Tab = '	'
DECLARE @KidsTable TABLE(Pos int IDENTITY, KidName sysname,KidID int,Color sysname)
INSERT INTO @KidsTable
SELECT wf.NextMenuPath,wf.NextMenuID,CASE WHEN Direction=1 THEN 'DARKGREEN' ELSE 'DARKRED' END Color FROM v_WorkFlow wf
INNER JOIN t_Workflow_Groups AS wfg ON wf.MenuID = wfg.MenuID AND wf.NextMenuID = wfg.NextMenuID
WHERE GroupName = @GroupName AND wf.MenuID = @MenuID AND wf.NextMenuID <> 25 ORDER BY NextWFIndex

DECLARE curKids CURSOR LOCAL READ_ONLY FOR SELECT KidName,KidID,Color FROM @KidsTable

INSERT INTO #ShowWFKids SELECT @GrandParentID,@MenuID,KidID,@Tabs FROM @KidsTable

---SELECT TOP 1 @Pos=Pos,@MenuName=KidName,@KidID=KidID,@Color=Color FROM @KidsTable WHERE Pos>@Pos
OPEN curKids
FETCH NEXT FROM curKids INTO @MenuName,@KidID,@Color

WHILE @@ROWCOUNT > 0 BEGIN
	DELETE FROM #ShowWFKids WHERE GrandParentID=@GrandParentID AND MenuID=@MenuID AND KidID=@KidID AND Tabs > @Tabs
	IF ( NOT EXISTS(SELECT * FROM #ShowWFKids WHERE GrandParentID=@GrandParentID AND MenuID=@MenuID AND KidID=@KidID AND Tabs<@Tabs) ) BEGIN
		--PRINT REPLICATE(@Tab,@Tabs) + '<MYTREE:TREENODE text="'+@MenuName+'">'--+CONVERT(varchar,@@NESTLEVEL)
		INSERT INTO #Nodes
		VALUES(REPLICATE(@Tab,@Tabs) +
			dbo.sprint6('<MYTREE:TREENODE DEFAULTSTYLE="COLOR:%s" tabID="%s.%s.%s" id="%s" text="%s">',@Color,@Tabs,@GrandParentID,@KidID,@KidID,@MenuName)
		)
		--INSERT INTO #ShowWFKids VALUES(@GrandParentID,@MenuID,@KidID,@Tabs)
		EXEC s_ShowWFKids @GroupName,@GrandParentID,@KidID,@Tabs_
		--PRINT REPLICATE(@Tab,@Tabs) + '</MYTREE:TREENODE>'+@CR
		INSERT INTO #Nodes VALUES(REPLICATE(@Tab,@Tabs) + '</MYTREE:TREENODE>'+@CR)
	END
	---SELECT TOP 1 @Pos=Pos,@MenuName=KidName,@KidID=KidID,@Color=Color FROM @KidsTable WHERE Pos>@Pos
	FETCH NEXT FROM curKids INTO @MenuName,@KidID,@Color
END

CLOSE curKids
DEALLOCATE curKids


